![]() |
Java Database Programming with JDBC
by Pratik Patel Coriolis, The Coriolis Group ISBN: 1576100561 Pub Date: 10/01/96 |
Previous | Table of Contents | Next |
As you can see, executeQuery and executeUpdate are simply helper methods for an application; they are built completely upon other methods contained within the class. The execute method accepts an SQL statement as its only parameter, and will be implemented differently, depending upon the underlying database system. For the SimpleText driver, the SQL statement will be parsed, prepared, and executed. Note that parameter markers are not allowed when executing an SQL statement directly. If the SQL statement created results containing columnar data, execute will return true; if the statement created a count of rows affected, execute will return false. If execute returns true, the application then uses getResultSet to return the current result information; otherwise, getUpdateCount will return the number of rows affected.
Warnings
As opposed to SQLException, which indicates a critical error, an SQLWarning can be issued to provide additional information to the application. Even though SQLWarning is derived from SQLException, warnings are not thrown. Instead, if a warning is issued, it is placed on a warning stack with the Statement object (the same holds true for the Connection and ResultSet objects). The application must then check for warnings after every operation using the getWarnings method. At first, this may seem a bit cumbersome, but when you consider the alternative of wrapping try...catch statements around each operation, this seems like a better solution. Note also that warnings can be chained together, just like SQLExceptions (for more information on chaining, see the JDBC Exception Types section earlier in this chapter).
Two (Or More) For The Price Of One
Some database systems allow SQL statements that return multiple results (columnar data or an update count) to be executed. If you are unfortunate enough to be developing a JDBC driver using one of these database systems, take heart. The JDBC specification does address this issue. The getMoreResults method is intended to move through the results. Figuring out when you have reached the end of the results, however, is a bit convoluted. To do so, you first call getMoreResults. If it returns true, there is another ResultSet present and you can use getResultSet to retrieve it. If getMoreResults returns false, you have either reached the end of the results, or an update count exists; you must call getUpdateCount to determine which situation exists. If getUpdateCount returns -1, you have reached the end of the results; otherwise, it will return the number of rows affected by the statement.
The SimpleText driver does not support multiple result sets, so I dont have any example code to present to you. The only DBMS that I am aware of that supports this is Sybase. Because there are already multiple JDBC drivers available for Sybase (one of which I have developed), I doubt you will have to be concerned with getMoreResults. Consider yourself lucky.
The PreparedStatement is used for pre-compiling an SQL statement, typically in conjunction with parameters, and can be efficiently executed multiple times with just a change in a parameter value; the SQL statement does not have to be parsed and compiled each time. Because the PreparedStatement class extends the Statement class, you will have already implemented a majority of the methods. The executeQuery, executeUpdate, and execute methods are very similar to the Statement methods of the same name, but they do not take an SQL statement as a parameter. The SQL statement for the PreparedStatement was provided when the object was created with the prepareStatement method from the Connection object. One danger to note here: Because PreparedStatement is derived from the Statement class, all of the methods in Statement are also in PreparedStatement. The three execute methods from the Statement class that accept SQL statements are not valid for the PreparedStatement class. To prevent an application from invoking these methods, the driver should also implement them in PreparedStatement, as shown here:
// The overloaded executeQuery on the Statement object (which we // extend) is not valid for PreparedStatement or CallableStatement // objects. public ResultSet executeQuery( String sql) throws SQLException { throw new SQLException("Method is not valid"); } // The overloaded executeUpdate on the Statement object (which we // extend) is not valid for PreparedStatement or CallableStatement // objects. public int executeUpdate( String sql) throws SQLException { throw new SQLException("Method is not valid"); } // The overloaded execute on the Statement object (which we // extend) is not valid for PreparedStatement or CallableStatement // objects. public boolean execute( String sql) throws SQLException { throw new SQLException("Method is not valid"); }
Setting Parameter Values
The PreparedStatement class introduces a series of set methods to set the value of a specified parameter. Take the following SQL statement:
INSERT INTO FOO VALUES (?, ?, ?)
If this statement was used in creating a PreparedStatement object, you would need to set the value of each parameter before executing it. In the SimpleText driver, parameter values are kept in a Hashtable. The Hashtable contains the parameter number as the key, and a CommonValue object as the data object. By using a CommonValue object, the application can set the parameter using any one of the supported data types, and we can coerce the data into the format that we need in order to bind the parameter. Heres the code for the setString method:
public void setString( int parameterIndex, String x) throws SQLException { // Validate the parameter index verify(parameterIndex); // Put the parameter into the boundParams Hashtable boundParams.put(new Integer(parameterIndex), x); }
The verify method validates that the given parameter index is valid for the current prepared statement, and also clears any previously bound value for that parameter index:
protected void verify( int parameterIndex) throws SQLException { clearWarnings(); // The paramCount was set when the statement was prepared if ((parameterIndex <= 0) || (parameterIndex > paramCount)) { throw new SQLException("Invalid parameter number: " + parameterIndex); } // If the parameter has already been set, clear it if (boundParams.get(new Integer(parameterIndex)) != null) { boundParams.remove(new Integer(parameterIndex)); } }
Previous | Table of Contents | Next |